# -*- coding: utf-8 -*-

#    Copyright (C) 2005-2012,2011 by Promotux
#                        di Francesco Meloni snc - http://www.promotux.it/

#    Author: Francesco Meloni  <francesco@promotux.it>

#    This file is part of Promogest.

#    Promogest is free software: you can redistribute it and/or modify
#    it under the terms of the GNU General Public License as published by
#    the Free Software Foundation, either version 2 of the License, or
#    (at your option) any later version.

#    Promogest is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.

#    You should have received a copy of the GNU General Public License
#    along with Promogest.  If not, see <http://www.gnu.org/licenses/>.




import ConfigParser
import time
from datetime import datetime
import sqlalchemy
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.interfaces import PoolListener
from sqlalchemy.ext.sqlsoup import SqlSoup
from sqlalchemy.interfaces import ConnectionProxy
from optparse import OptionParser

import csv

def stringToDate(stringa):
    """
    Converte una stringa in data
    """
    if stringa is None or stringa == '':
        return None
    else:
        try:
            d = time.strptime(stringa, "%d/%m/%Y")
            data = datetime(d[0], d[1], d[2])
        except Exception:
            data=None
        return data

class MyProxy(ConnectionProxy):
    def cursor_execute(self, execute, cursor, statement, parameters, context, executemany):
        return execute(cursor, statement, parameters, context)

class SetTextFactory(PoolListener):
     def connect(self, dbapi_con, con_record):
         dbapi_con.text_factory = str

class GenericFillData(object):
    def __init__(self):
        usage = """Uso: %prog [options]
        Opzioni disponibili sono :
                -f   --fileCSV Il file da importare
                """
        parser = OptionParser(usage=usage)
        parser.add_option("-f","--file",
                            help="file dei dati in formato csv",
                            type="string",
                            dest="fileDB")
        config = ConfigParser.RawConfigParser()
        config.read('zucchetti.cfg')
        self.azienda =  config.get("PRO","azienda")
        self.db_name = config.get("PRO","db_name")
        self.port = config.get("PRO","port")
        self.tipo = config.get("TypeDB","tipo")
        self.user = config.get("PRO","user")
        self.password = config.get("PRO","password")
        self.mainSchema = config.get("PRO","mainschema")
        self.host = config.get("PRO","host")
        #self.file_csv = config.get("FileCSV","file_csv")
        self.db_path = config.get("ONE","db_path")
        self.delimiter = config.get("FileCSV","delimiter")
        self.quotechar = config.get("FileCSV","quotechar")

        (options, args) = parser.parse_args()
        if options.fileDB and options.fileDB != "":
            self.file_csv = options.fileDB
        else:
            print "ERRORE NESSUN FILE DA PARSARE"
            raise Exception("ERRORE: usare -f /path/to/file")
        self.connection()

    def connection(self):
        if self.tipo == "postgresql":
            engine = create_engine('postgres:'+'//'+self.user+':'
                                + self.password+ '@'
                                + self.host + ':'
                                + self.port + '/'
                                + self.db_name,
                                encoding='utf-8',
                                convert_unicode=True )
        else:
            self.azienda = None
            self.mainSchema = None
            engine =create_engine("sqlite:///"+self.db_path+"db",listeners=[SetTextFactory()],proxy=MyProxy())
        engine.echo = False
        meta = MetaData(engine)
        self.pg_db_dest = SqlSoup(meta)
        self.pg_db_dest.schema = self.azienda
        self.readFile()

    def readFile(self):
        spamReader = list(csv.reader(open(self.file_csv), delimiter=self.delimiter, quotechar=self.quotechar))
#        spamReader.next() # Skip header line.
        spamReaderList = spamReader[1:]
        if len(spamReader) >0:
            tabella = self.definisciTabella(spamReader[0])
            if tabella =="gruppomerceologico":
                self.fillFamigliaArticolo(spamReaderList)
            elif tabella == "articolo":
                self.fillArticolo(spamReaderList)
            elif tabella == "listino_articolo":
                self.fillListinoArticolo(spamReaderList)
        #print "OOOOOOOOOOOOOOOO", spamReaderList
        #self.fillDataContact(spamReaderList)

    def definisciTabella(self, dati):
        if dati:
            if dati[0] =="gmcodice" and dati[1] == "gmdescri":
                print "TABELLA GRUPPO MERCEOLOGICO"
                return "gruppomerceologico"
            elif dati[0] =="arcodart" and dati[1] == "ardesart":
                print "TABELLA ARTICOLO"
                return "articolo"
            elif dati[0] == "arcodart" and dati[1] == "lscodlis":
                print "TABELLA LISTINO ARTICOLO"
                return "listino_articolo"
            else:
                print "ATTENZIONE TABELLA NON RICONOSCIUTA"

    def fillFamigliaArticolo(self, spamReaderList):
        """ SCHEMA CONVERSIONE gruppo merceologico:
        zucchetti : gruppomerceologico = promogest : famiglia articolo
        zucchetti 2 colonne
        promogest 3 colonne
        zucchetti : gmcodice = promogest : codice
        zucchetti : gmdescri = promogest :denominazione
        zucchetti : gmdescri[0:10] = promogest : denominazione_breve
        """
        for row in spamReaderList:
            a = self.pg_db_dest.famiglia_articolo.filter_by(codice=row[0]).all()
            if not a:
                a = self.pg_db_dest.famiglia_articolo()
            else:
                a = a[0]
            a.codice = row[0]
            a.denominazione = row[1]
            a.denominazione_breve = row[1][0:10]
            sqlalchemy.ext.sqlsoup.Session.add(a)
            sqlalchemy.ext.sqlsoup.Session.commit()

    def fillArticolo(self, spamReaderList):
        """ SCHEMA CONVERSIONE articolo:
        zucchetti : articoli = promogest : articolo
        0 zucchetti : arcodart = pg2 : codice
        1 zucchetti : ardesart = pg2:d enominazione
        2 zucchetti : argrumer = pg2 : id_famiglia_articolo
        3 zucchetti : arcatomo = pg2 : ??????????????
        4 zucchetti : arcodfam = pg2 : ??????????????
        5 zucchetti : arcatcon = Non rilevante
        6 zucchetti : arcodiva = pg2 : id_aliquota_iva
        7 zucchetti : arunmis1 = pg2 : id_unita_base
        "0000R";"OLIO RITONIFICANTE LT1";"0004";"00204";"2048";"ALTRA";"20";"LT"
        """
        for row in spamReaderList:
            a = self.pg_db_dest.articolo.filter_by(codice=row[0]).all()
            if not a:
                a = self.pg_db_dest.articolo()
            else:
                a = a[0]
            a.codice = row[0]
            a.denominazione = row[1]

            f = self.pg_db_dest.famiglia_articolo.filter_by(codice=row[2]).all()
            if not f:
                print "FAMIGLIA NON PRESENTE"
                del a
                continue
            a.id_famiglia_articolo = f[0].id
            g = self.pg_db_dest.aliquota_iva.filter_by(percentuale=float(row[6])).one()
            if not g:
                print "ATTENZIONE AGGIHNGERE IVA AL %s" %row[6]
                raise Exception("ATTENZIONE AGGIHNGERE IVA AL %s") %row[6]
            else:
                iva = g.id
            a.id_aliquota_iva = iva
            um = row[7]
            if um =="LT":
                umid = 3
            elif um == "MT":
                umid =2
            elif um =="NR":
                umid = 5
            elif um =="GR" or um =="KG":
                umid =4
            a.id_unita_base = umid
            a.id_categoria_articolo = 1
            a.cancellato = False
            sqlalchemy.ext.sqlsoup.Session.add(a)
        sqlalchemy.ext.sqlsoup.Session.commit()


    def fillListinoArticolo(self, spamReaderList):
        """ SCHEMA CONVERSIONE listino_articolo:
        0 zucchetti : arcodart = pg2 : "mi serve per agganciare id_articolo"
        1 zucchetti : lscodlis = pg2 : mi serve per agganciare id_listino
        2 zucchetti : lidatatt = pg2 : data_listino
        3 zucchetti : liprezzo = pg2 : prezzo_ingrosso
        """
        for row in spamReaderList:
            a = self.pg_db_dest.articolo.filter_by(codice=row[0]).all()
            if not a:
                print "ARTICOLO NON PRESENTE"
                del a
                continue
            else:
                idArticolo = a[0].id

            b = self.pg_db_dest.listino_articolo.filter_by(id_articolo=idArticolo, id_listino=row[1]).all()
            if not b:
                b = self.pg_db_dest.listino_articolo()
            else:
                b = a[0]
            print row[2][0:-5],row[3].replace(",",".")
            b.id_listino = int(row[1])
            b.id_articolo = idArticolo
            b.prezzo_ingrosso = float(row[3].replace(",",".") or "0")
            b.data_listino_articolo =  stringToDate(row[2][0:-5])
            b.listino_attuale = True
            sqlalchemy.ext.sqlsoup.Session.add(b)
        sqlalchemy.ext.sqlsoup.Session.commit()



if __name__ == "__main__":
    GenericFillData()
